package com.mj.postcode.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.mj.postcode.bean.Postcode;
import com.mj.postcode.util.DBManager;

/**
 * 邮编查询dao
 * @author zhaominglei
 * @date 2015-11-10
 * 
 */
public class PostcodeDao extends BaseDao {
	private DBManager dbManager = new DBManager();
	
	public Postcode getPostcodeByCode(Context context, String code) {
		if (code != null && !code.equals("")) {
			Postcode postcode = new Postcode();
			SQLiteDatabase db = dbManager.openDatabase(context);
			Cursor cursor = db.rawQuery("select s.SFname,d.DQname,q.QXname,z.ZIPname,z.ZIPcode from ziptable z,sftable s,dqtable d,qxtable q where z.sfcode=s.sfcode and z.dqcode=d.dqcode and z.qxcode=q.qxcode and z.zipcode = ?", new String[]{ code });
			if (cursor.moveToFirst()) {
				postcode.setProvince(cursor.getString(cursor.getColumnIndex("SFname")));
				postcode.setCity(cursor.getString(cursor.getColumnIndex("DQname")));
				postcode.setDistrict(cursor.getString(cursor.getColumnIndex("QXname")));
				postcode.setAddress(cursor.getString(cursor.getColumnIndex("ZIPname")));
				postcode.setZipcode(cursor.getString(cursor.getColumnIndex("ZIPcode")));
			}
			cursor.close();
			return postcode;
		}
		return null;
	}
	
	public List<String> getProvinces(Context context) {
		List<String> provinces = new ArrayList<String>();
		SQLiteDatabase db = dbManager.openDatabase(context);
		Cursor cursor = db.rawQuery("select * from sftable", null);
		while (cursor.moveToNext()) {
			String sfname = cursor.getString(cursor.getColumnIndex("SFname"));
			if (sfname != null && !sfname.equals("")) {
				provinces.add(sfname);
			}
		}
		cursor.close();
		return provinces;
	}
	
	public List<String> getCities(Context context, String province) {
		List<String> cities = new ArrayList<String>();
		if (province != null && !province.equals("")) {
			SQLiteDatabase db = dbManager.openDatabase(context);
			Cursor cursor = db.rawQuery("select d.* from dqtable d,sftable s where d.SFcode=s.SFcode and s.SFname=?", new String[]{ province });
			while (cursor.moveToNext()) {
				String dqname = cursor.getString(cursor.getColumnIndex("DQname"));
				if (dqname != null && !dqname.equals("")) {
					cities.add(dqname);
				}
			}
			cursor.close();
		}
		return cities;
	}
	
	public List<String> getDistricts(Context context, String province, String city) {
		List<String> districts = new ArrayList<String>();
		if (province != null && !province.equals("") && city != null && !city.equals("")) {
			SQLiteDatabase db = dbManager.openDatabase(context);
			Cursor cursor = db.rawQuery("select q.QXname from qxtable q,dqtable d,sftable s where q.SFcode=s.SFcode and q.DQcode=d.DQcode and s.SFname=? and d.DQname=?", new String[]{ province , city });
			while (cursor.moveToNext()) {
				String qxname = cursor.getString(cursor.getColumnIndex("QXname"));
				if (qxname != null && !qxname.equals("")) {
					districts.add(qxname);
				}
			}
			cursor.close();
		}
		return districts;
	}
	
	public List<Postcode> getCodeInfo(Context context, String province, String city, String district, String address) {
		if (province != null && !province.equals("") 
				&& city != null && !city.equals("") 
				&& district != null && !district.equals("")) {
			List<Postcode> postcodes = new ArrayList<Postcode>();
			SQLiteDatabase db = dbManager.openDatabase(context);
			Cursor cursor = db.rawQuery("select s.SFname,d.DQname,q.QXname,z.ZIPname,z.ZIPcode from ziptable z,sftable s,dqtable d,qxtable q where z.sfcode=s.sfcode and z.dqcode=d.dqcode and z.qxcode=q.qxcode and s.sfname=? and d.dqname=? and q.qxname=? and z.zipname like ?", new String[]{ province, city, district,"%"+address+"%" });
			while (cursor.moveToNext()) {
				Postcode postcode = new Postcode();
				postcode.setProvince(cursor.getString(cursor.getColumnIndex("SFname")));
				postcode.setCity(cursor.getString(cursor.getColumnIndex("DQname")));
				postcode.setDistrict(cursor.getString(cursor.getColumnIndex("QXname")));
				postcode.setAddress(cursor.getString(cursor.getColumnIndex("ZIPname")));
				postcode.setZipcode(cursor.getString(cursor.getColumnIndex("ZIPcode")));
				postcodes.add(postcode);
			}
			cursor.close();
			return postcodes;
		}
		return null;
	}
}
